﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Librerias;
using System.Collections;




namespace Librerias
{
    /// <summary>
    /// Clase que persiste los Eventos
    /// </summary>
    public class CADEventoAlumno
    {
        SqlConnection con;

        /// <summary>
        /// Constructor de CADEvento. 
        /// Únicamente establece la conexión con la base de datos
        /// </summary>
        public CADEventoAlumno()
        {
            con = new SqlConnection(CADConexion.connString);
        }

        /// <summary>
        /// A partir de un alumno se obtiene sus eventos.
        /// </summary>
        /// <param name="idAlu"></param>
        /// <returns></returns>
        public DataTable ConsultaEventosAlumno(string Alumno)
        {
            DataTable res = new DataTable();

            try
            {
                con.Open();
                string select = "select * from eventoalu where evealuQuien = '" + Alumno
                                + "' order by evealuDate desc";
                SqlDataAdapter adaptadorSQL = new SqlDataAdapter(select, con);
                adaptadorSQL.Fill(res);

            }
            catch (SqlException ex)
            {
                res = null;
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return res;
        }

        /// <summary>
        /// A partir de un alumno obtiene sus eventos en funcion de un dia
        /// </summary>
        public DataTable ConsultaEventosAlumnoDia(string alumno, string cadena)
        {
            DataTable res = new DataTable();

            try
            {
                con.Open();
                string select = "select eventoasig.eveAsigId, eventoasig.eveAsigCorreo, eventoasig.eveAsigCodigo, eventoasig.eveAsigDate, eventoasig.eveAsigAsunto, eventoasig.eveAsigTexto, " +
                "eventoasig.eveAsigF_ini, eventoasig.eveAsigF_fin " +
                "from eventoasig INNER JOIN " +
                "(select cursar.curCodigo AS asig " +
                "from cursar INNER JOIN " +
                "asignatura ON cursar.curCodigo = asignatura.asiCodigo " +
                "where (cursar.curCorreo = '" + alumno + "')) AS misasigs ON eventoasig.eveAsigCodigo = misasigs.asig AND CONVERT(varchar, eventoasig.eveAsigF_ini,20) LIKE '%" + FormateaFecha(cadena) + "%'" +
                "UNION " +
                "select evealuId, evealuQuien, 'Alumno' AS Expr1, evealuDate, evealuAsunto, evealuTexto, evealuF_ini, evealuF_fin " +
                "from eventoalu " +
                "where (evealuQuien = '" + alumno + "') AND (CONVERT(varchar(20), evealuF_ini, 20) LIKE '%" + FormateaFecha(cadena) + "%')" +
                "ORDER BY eventoasig.eveAsigDate DESC";

                SqlDataAdapter adaptadorSQL = new SqlDataAdapter(select, con);
                adaptadorSQL.Fill(res);

            }
            catch (SqlException ex)
            {
                res = null;
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return res;
        }

        public DataTable ConsultaEventosProfesorDia(string profesor, string cadena)
        {
            DataTable res = new DataTable();

            try
            {
                con.Open();
                string select = "select eventoasig.eveAsigId, eventoasig.eveAsigCorreo, eventoasig.eveAsigCodigo, eventoasig.eveAsigDate, eventoasig.eveAsigAsunto, eventoasig.eveAsigTexto, " +
                "eventoasig.eveAsigF_ini, eventoasig.eveAsigF_fin " +
                "from eventoasig INNER JOIN " +
                "(select turno.turCodigo AS asig " +
                "from turno INNER JOIN " +
                "asignatura ON turno.turCodigo = asignatura.asiCodigo " +
                "where (turno.turCorreo = '" + profesor + "')) AS misasigs ON eventoasig.eveAsigCodigo = misasigs.asig AND CONVERT(varchar, eventoasig.eveAsigF_ini,20) LIKE '%" + FormateaFecha(cadena) + "%'" +
                "UNION " +
                "select evealuId, evealuQuien, 'Alumno' AS Expr1, evealuDate, evealuAsunto, evealuTexto, evealuF_ini, evealuF_fin " +
                "from eventoalu " +
                "where (evealuQuien = '" + profesor + "') AND (CONVERT(varchar(20), evealuF_ini, 20) LIKE '%" + FormateaFecha(cadena) + "%')" +
                "ORDER BY eventoasig.eveAsigDate DESC";

                SqlDataAdapter adaptadorSQL = new SqlDataAdapter(select, con);
                adaptadorSQL.Fill(res);

            }
            catch (SqlException ex)
            {
                res = null;
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return res;
        }



        /// <summary>
        /// Entra una fecha en formato 12/03/2012 07:01 y sale 2012-03-12 07:01
        /// </summary>
        private string FormateaFecha(string entrada)
        {
            //Elimina espacios al principio y al final.
            entrada = entrada.Trim();

            string dia = "", mes = "", ano = "", res = entrada;



            int i = 0;
            if (entrada.Contains("/"))
            {

                //Extrae dia
                while (i < entrada.Length && entrada[i] != '/')
                {
                    dia += entrada[i];
                    i++;
                }
                if (dia.Length == 1)
                {
                    dia = "0" + dia;
                }
                i++;

                //Extrae mes
                while (i < entrada.Length && entrada[i] != '/')
                {
                    mes += entrada[i];
                    i++;
                }
                if (mes.Length == 1)
                {
                    mes = "0" + mes;
                }
                i++;

                //Extrae ano
                while (i < entrada.Length && entrada[i] != ' ')
                {
                    ano += entrada[i];
                    i++;
                }

                res = ano + "-" + mes + "-" + dia;

                if (entrada.Contains(':'))
                {
                    while (i < entrada.Length)
                    {
                        res += entrada[i];
                        i++;
                    }
                }
            }

            return res;
        }

        public int CrearEventoAlumno(ENEventoAlumno eve)
        {
            int res = -1;

            try
            {
                con.Open();

                SqlCommand com = new SqlCommand("insert into eventoalu (evealuDate,evealuQuien, evealuAsunto, evealutexto, evealuF_ini, evealuF_fin) values('" +
                                                    DateTime.Now.ToString() + "','" +
                                                    eve.Alumno + "','" +
                                                    eve.Asunto + "','" +
                                                    eve.Texto + "','" +
                                                    eve.FechaInicio + "','" +
                                                    eve.FechaFin + "');", con);
                res = com.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return res;
        }

        /// <summary>
        /// Elimina un evento a partir de su ID
        /// </summary>
        /// <param name="idEventoAlu"></param>
        /// <returns></returns>
        public int EliminarEventoAlumno(int idEventoAlu)
        {
            int res = -1;

            try
            {
                con.Open();

                SqlCommand com = new SqlCommand("delete from eventoalu where evealuId = '" + idEventoAlu + "'", con);
                res = com.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return res;
        }

        /// <summary>
        /// Actualiza un evento
        /// </summary>
        /// <param name="datos"></param>
        /// <returns></returns>
        public int ModificarEventoAlumno(ENEventoAlumno datos)
        {
            int res = -1;

            try
            {
                con.Open();

                SqlCommand com = new SqlCommand("update eventoalu set " +
                                                "evealuAsunto='" + datos.Asunto +
                                                "',evealuTexto='" + datos.Texto +
                                                "', evealuF_ini='" + datos.FechaInicio +
                                                "', evealuF_fin='" + datos.FechaFin +
                                                "' where evealuId='" + datos.Id + "';", con);
                res = com.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return res;
        }
    }
}
